# import pymysql


# def getconn(host, user, passwd, db, sql, port=3306,charset='utf8'):
#     conn = pymysql.connect(host=host, user=user, passwd=passwd, port=port, db=db, charset=charset)  #建立连接
#     cur = conn.cursor(cursor=pymysql.cursors.DictCursor)        #建立游标并指定游标类型
#     cur.execute(sql)                              #执行sql
#     if sql.startswith('select'):                    #判断sql是否是select
#         res = cur.fetchone()
#     else:
#         conn.commit()                   #insert\delete\update语句执行完毕后需要进行commit
#         res = 88
#     cur.close()                        #关闭游标
#     conn.close()                       #关闭连接
#     return res

# import MySQLdb
# from DBUtils.PooledDB import PooledDB
# pool = PooledDB(MySQLdb,5,host='localhost',user='root',passwd='pwd',db='myDB',port=3306) #5为连接池里的最少连接数

# conn = pool.connection()  #以后每次需要数据库连接就是用connection（）函数获取连接就好了
# cur=conn.cursor()
# SQL="select * from table1"
# r=cur.execute(SQL)
# r=cur.fetchall()
# cur.close()
# conn.close()


# from config import *
from psycopg2 import pool


class PostgreSql:
    def __init__(self):
        try:
            self.connectPool = pool.SimpleConnectionPool(2, 10, host="124.70.195.145", port="16800",
                                                         user="postgres", password="Admin@2021",
                                                         database="smc", keepalives=1,
                                                         keepalives_idle=30, keepalives_interval=10,
                                                         keepalives_count=5)
        except Exception as e:
            print(e)

    def getConnect(self):
        conn = self.connectPool.getconn()
        cursor = conn.cursor()
        return conn, cursor

    def closeConnect(self, conn, cursor):
        cursor.close()
        self.connectPool.putconn(conn)

    def closeAll(self):
        self.connectPool.closeall()

    # 执行增删改
    def execute(self, sql, value=None):
        conn, cursor = self.getConnect()
        try:
            res = cursor.execute(sql, value)
            conn.commit(conn, cursor)
            self.closeConnect(conn, cursor)
            return res
        except Exception as e:
            conn.rollock()
            raise e

    def selectOne(self, sql):
        conn, cursor = self.getConnect()
        cursor.execute(sql)
        result = cursor.fetchone()
        self.closeConnect(conn, cursor)
        return result

    def selectAll(self, sql):
        conn, cursor = self.getConnect()
        cursor.execute(sql)
        result = cursor.fetchall()
        self.closeConnect(conn, cursor)
        return result
